package com.work.dao;/**
 * @author： li
 * @date： 2021-05-28 18:43
 * @version 1.0
 */

import com.work.pojo.email;
import com.work.pojo.user;
import com.work.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author： Administrator
 * @date： 2021-05-28 18:43
 * @version 1.0
 */
public class emaildao {
    //邮件写入
    public static int insert(email us) throws ClassNotFoundException, SQLException {
        int i=0;
// 1，获取连接.
        try {
            Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
            String sql = "insert into EMAIL (id,title,content,cookies) values(SEQ_MAIL_ID.nextval,?,?,?)";
            //2，2创建prepareStatement对象，执行 sqL语句
            PreparedStatement ps = con.prepareStatement(sql);
            //3.给占位符进行赋值
            ps.setString(1, us.getTitle());
            ps.setString(2, us.getContent());
            ps.setString(3,us.getCookies());
            i = ps.executeUpdate();
//3.资源释放
            DBUtil.close(ps,con);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    public static int insert1(email us) throws ClassNotFoundException, SQLException {
        int i=0;
// 1，获取连接.
        try {
            Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
            String sql = "insert into EMAIL values(SEQ_MAIL_ID.nextval,null,null,?,date=sysdate)";
            //2，2创建prepareStatement对象，执行 sqL语句
            PreparedStatement ps = con.prepareStatement(sql);
            //3.给占位符进行赋值
            ps.setString(1,us.getCookies());
            i = ps.executeUpdate();
//3.资源释放
            DBUtil.close(ps,con);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return i;
    }
    //邮件查询
    public static List<email> select() throws ClassNotFoundException, SQLException {
        //0.创建集合
        List<email> user=new ArrayList<>();
        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from EMAIL";
        PreparedStatement ps = con.prepareStatement(sql1);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            int id=rs.getInt("id");
            String title=rs.getString("title");
            String content=rs.getString("content");
            String cookies=rs.getString("cookies");
            String time=rs.getString("time");
            //控制台输出
          /*  System.out.println("id："+id+"，username："+username+
                    "，password："+password+"，state："+state);*/
           email user1 =new email(id,title,content,cookies,time);
            user.add(user1);
        }
        DBUtil.close(rs,ps,con);
        return user;
    }
    //查询插入时间
    //通过cookies查询邮件
    public static List<email> selectbycookies(email em) throws ClassNotFoundException, SQLException {
        //0.创建集合
        List<email> user=new ArrayList<>();
        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from EMAIL where COOKIES=? order by TIME desc";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,em.getCookies());
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            int id=rs.getInt("id");
            String title=rs.getString("title");
            String content=rs.getString("content");
            String cookies=rs.getString("cookies");
            String time=rs.getString("time");
         // System.out.println("id："+id+"，username："+title+
           //         "，password："+content+"，state："+cookies);
            email user1 =new email(id,title,content,cookies,time);
            user.add(user1);
        }
        DBUtil.close(rs,ps,con);
        return user;
    }
//通过id删除邮件
    public static int deletebyid(email user) throws SQLException {

        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql2= "delete from EMAIL where ID=?";
        PreparedStatement ps = con.prepareStatement(sql2);
        ps.setInt(1, user.getId());
        int i = ps.executeUpdate();
        DBUtil.close(ps,con);
        return i;
    }

    /**
     * 查询 cookies所有的行数
     * @return
     * @throws SQLException
     */
    public static Integer countNum(email em) throws SQLException {
        int count = 0;
        Connection con = DBUtil.getConnection();
        String sql = "select count(*) cn from EMAIL where COOKIES=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,em.getCookies());
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            count = rs.getInt("cn");
        }
        DBUtil.close(rs,ps,con);
        return count;
    }

    public static email selectbyid(email em) throws SQLException {
        //0.创建集合
       // List<email> user=new ArrayList<>();
        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from EMAIL where ID=?";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,em.getCookies());
        ResultSet rs = ps.executeQuery();
        email email=new email();
        while(rs.next()){
            int id=rs.getInt("id");
            String title=rs.getString("title");
            String content=rs.getString("content");
            String cookies=rs.getString("cookies");
            String time=rs.getString("time");
            // System.out.println("id："+id+"，username："+title+
            //         "，password："+content+"，state："+cookies);
            email=new email(id,title,content,cookies,time);
            //user.add(user1);
        }
        DBUtil.close(rs,ps,con);
        return email;
    }
}
